-- ============================================================
-- DataDrop L3 Database — Queries in Access
-- Lesson 3: Queries in Access (GUI)
-- Richer dataset: 28 songs across 7 genres for meaningful querying
-- ============================================================

CREATE TABLE Songs (
    SongID       INTEGER     NOT NULL,
    Title        TEXT(100)   NOT NULL,
    Artist       TEXT(80)    NOT NULL,
    Genre        TEXT(30)    NOT NULL,
    ReleaseYear  INTEGER     NOT NULL,
    DurationMins DOUBLE      NOT NULL,
    Streams      INTEGER     NOT NULL,
    IsExplicit   YESNO       NOT NULL,
    CONSTRAINT PK_Songs PRIMARY KEY (SongID)
);

INSERT INTO Songs VALUES (1,  'Neon Skyline',        'The Midnight',      'Synthpop',  2021, 4.2,  2340000, FALSE);
INSERT INTO Songs VALUES (2,  'Ocean Drive',          'Duke Dumont',       'Electronic',2014, 3.8,  5100000, FALSE);
INSERT INTO Songs VALUES (3,  'Bad Habits',           'Ed Sheeran',        'Pop',       2021, 3.7,  9800000, FALSE);
INSERT INTO Songs VALUES (4,  'Blinding Lights',      'The Weeknd',        'Pop',       2019, 3.4, 12000000, FALSE);
INSERT INTO Songs VALUES (5,  'Heat Waves',           'Glass Animals',     'Indie',     2020, 3.9,  8500000, FALSE);
INSERT INTO Songs VALUES (6,  'Industry Baby',        'Lil Nas X',         'Hip-Hop',   2021, 3.6,  7200000, TRUE);
INSERT INTO Songs VALUES (7,  'Levitating',           'Dua Lipa',          'Pop',       2020, 3.5,  6800000, FALSE);
INSERT INTO Songs VALUES (8,  'Stay',                 'Kid Laroi',         'Pop',       2021, 2.5,  9100000, TRUE);
INSERT INTO Songs VALUES (9,  'Wellerman',            'Nathan Evans',      'Folk',      2021, 2.2,  3200000, FALSE);
INSERT INTO Songs VALUES (10, 'Good 4 U',             'Olivia Rodrigo',    'Pop-Rock',  2021, 2.9,  7600000, FALSE);
INSERT INTO Songs VALUES (11, 'Peaches',              'Justin Bieber',     'R&B',       2021, 3.2,  5500000, TRUE);
INSERT INTO Songs VALUES (12, 'Montero',              'Lil Nas X',         'Pop',       2021, 2.3,  8000000, TRUE);
INSERT INTO Songs VALUES (13, 'Solar Power',          'Lorde',             'Indie',     2021, 3.8,  2100000, FALSE);
INSERT INTO Songs VALUES (14, 'Shivers',              'Ed Sheeran',        'Pop',       2021, 3.3,  5900000, FALSE);
INSERT INTO Songs VALUES (15, 'Permission to Dance',  'BTS',               'K-Pop',     2021, 3.2,  6300000, FALSE);
INSERT INTO Songs VALUES (16, 'Leave the Door Open',  'Silk Sonic',        'R&B',       2021, 4.0,  4700000, FALSE);
INSERT INTO Songs VALUES (17, 'Easy On Me',           'Adele',             'Pop',       2021, 3.4, 10200000, FALSE);
INSERT INTO Songs VALUES (18, 'Watermelon Sugar',     'Harry Styles',      'Pop',       2019, 2.8,  7800000, FALSE);
INSERT INTO Songs VALUES (19, 'drivers license',      'Olivia Rodrigo',    'Pop',       2021, 4.1,  9400000, FALSE);
INSERT INTO Songs VALUES (20, 'Miracle',              'Calvin Harris',     'Electronic',2022, 3.6,  3100000, FALSE);
INSERT INTO Songs VALUES (21, 'The Less I Know',      'Tame Impala',       'Indie',     2015, 3.6,  4500000, FALSE);
INSERT INTO Songs VALUES (22, 'Circles',              'Post Malone',       'Hip-Hop',   2019, 3.3,  8900000, FALSE);
INSERT INTO Songs VALUES (23, 'Midnight Rain',        'Taylor Swift',      'Pop',       2022, 5.5,  3700000, FALSE);
INSERT INTO Songs VALUES (24, 'The Box',              'Roddy Ricch',       'Hip-Hop',   2019, 3.2,  7100000, TRUE);
INSERT INTO Songs VALUES (25, 'Physical',             'Dua Lipa',          'Pop',       2020, 3.7,  5200000, FALSE);
INSERT INTO Songs VALUES (26, 'Dynamite',             'BTS',               'K-Pop',     2020, 3.2,  8400000, FALSE);
INSERT INTO Songs VALUES (27, 'Butter',               'BTS',               'K-Pop',     2021, 2.7,  7700000, FALSE);
INSERT INTO Songs VALUES (28, 'The Night We Met',     'Lord Huron',        'Folk',      2014, 3.3,   950000, FALSE);

-- ============================================================
-- QUERY ANSWERS — Teacher reference
-- Students create these queries using the QBE grid
-- ============================================================

-- TASK 1: Show all fields for all songs in the Pop genre
-- SELECT * FROM Songs WHERE Genre = "Pop";
-- Expected result: 11 records

-- TASK 2: Show Title, Artist, Streams for songs with over 5 million streams, sorted by Streams DESC
-- SELECT Title, Artist, Streams FROM Songs WHERE Streams > 5000000 ORDER BY Streams DESC;
-- Expected result: 19 records

-- TASK 3: Show all fields for songs with duration between 3 and 4 minutes (inclusive)
-- SELECT * FROM Songs WHERE DurationMins >= 3 AND DurationMins <= 4;
-- Or: SELECT * FROM Songs WHERE DurationMins BETWEEN 3 AND 4;
-- Expected result: 17 records

-- TASK 4: Show Title, Artist, Genre for Pop OR Electronic songs only
-- SELECT Title, Artist, Genre FROM Songs WHERE Genre = "Pop" OR Genre = "Electronic";
-- Expected result: 13 records

-- TASK 5: Show all songs where the Title starts with "The"
-- SELECT * FROM Songs WHERE Title LIKE "The*";
-- Expected result: 3 records (The Less I Know, The Box, The Night We Met)

-- TASK 6: Show Title, Artist for songs from 2019 or earlier (ReleaseYear <= 2019)
-- SELECT Title, Artist FROM Songs WHERE ReleaseYear <= 2019;
-- Expected result: 8 records

-- EXTENSION — Parameterised query:
-- SELECT * FROM Songs WHERE Genre = [Enter genre:];
-- When run, Access prompts the user for input. Students type a genre and see filtered results.

-- ============================================================
-- TEACHER NOTES:
-- Before the lesson, create DataDrop_L3.accdb with this data.
-- Students work through 6 tasks using the Query Design grid (QBE).
-- They should NOT be typing SQL yet — that's L4.
-- The extension parameterised query is a nice "wow" moment —
-- show students how typing [message] in the Criteria row creates an input prompt.
-- ============================================================
